﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Audit_Configuration_Oracle.Model;
using Oracle.DataAccess.Client;
using System.Data;
using System.Windows.Forms;

namespace Audit_Configuration_Oracle.DAL
{
    public class AuditRulesDAL
    {
        private static string[] TARGET_TABLES = { "DBA_STMT_AUDIT_OPTS", "DBA_PRIV_AUDIT_OPTS", "DBA_OBJ_AUDIT_OPTS" };
        private static string ACTIVE_RULES_OBJ = "SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, SEL, INS, UPD, DEL FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER {0}";
        private static string ACTIVE_RULES_STMT = "SELECT USER_NAME, AUDIT_OPTION, SUCCESS, FAILURE FROM DBA_STMT_AUDIT_OPTS WHERE USER_NAME {0}";
        private static string ACTIVE_RULES_PRIV = "SELECT USER_NAME, PRIVILEGE, SUCCESS, FAILURE FROM DBA_PRIV_AUDIT_OPTS WHERE USER_NAME {0}";        

        public static List<Dictionary<string,string>> GetAuditModalities()
        {
            List<Dictionary<string,string>> modalities = null;
            DBConnectionSing db = null;
            string targetSentence = string.Empty;
            try
            {
                db = DBConnectionSing.Db;
                db.openConnection();

                modalities = new List<Dictionary<string, string>>();
                for (int i = 0; i < TARGET_TABLES.Length; i++)
                {
                    targetSentence = TARGET_TABLES[i];
                    if (CheckForActiveRules(CreateSentence(targetSentence), db))
                    {
                        modalities.Add(SetModality(targetSentence));
                    }
                }                
            }
            catch (OracleException e)
            {
                throw e;
            }
            finally
            {
                db.closeConnection();
                db = null;
            }

            return modalities;
        }

        private static Dictionary<string, string> SetModality(string targetTable)
        {
            Dictionary<string, string> res = new Dictionary<string,string>();
            string userText = string.Empty;
            switch (targetTable)
            {
                case "DBA_STMT_AUDIT_OPTS":
                    userText = "Sentencias" ;                         
                    break;
                case "DBA_PRIV_AUDIT_OPTS":
                    userText = "Privilegios"; 
                    break;
                case "DBA_OBJ_AUDIT_OPTS":
                    userText = "Objetos"; 
                    break;
            }
            res.Add("text",userText);
            res.Add("value",targetTable);
            return res;
        }

        private static string CreateSentence(string targetTable)
        {
            string sql = string.Empty;
            string condition = string.Empty;
            switch(targetTable)
            {
                case "DBA_STMT_AUDIT_OPTS":
                    condition = "USER_NAME <> 'NULL'";
                    break;
                case "DBA_PRIV_AUDIT_OPTS":
                    condition = "USER_NAME <> 'NULL'";
                    break;
                case "DBA_OBJ_AUDIT_OPTS":
                    condition = "OWNER <> 'NULL'";
                    break;
            }
            sql = string.Format("SELECT COUNT(*) FROM {0} WHERE {1}",targetTable,condition);

            return sql;
        }

        private static bool CheckForActiveRules(string targetRule, DBConnectionSing db)
        {
            bool hasRules = false;             
            try
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = db.conn;
                    cmd.CommandText = targetRule;

                    int res = int.Parse(cmd.ExecuteScalar().ToString());

                    hasRules = res > 0;                     
                }
            }
            catch (OracleException e) { throw e; }
            catch (Exception e) { throw e; }
            finally{}
            return hasRules;
        }


        public static DataSet GetActiveRulesObjects(string user)
        {
            DataSet ds = null;
            DataGridView dg = new DataGridView();
            DataTable dt = new DataTable("TableActiveRulesObjects");


            dt.Columns.Add("ACTION", typeof(bool));
            dt.Columns.Add("PROPIETARIO", typeof(string));
            dt.Columns.Add("NOMBRE OBJETO", typeof(string));
            dt.Columns.Add("TIPO OBJETO", typeof(string));
            dt.Columns.Add("SUCCESS SEL", typeof(string));
            dt.Columns.Add("ACCESS SEL", typeof(string));
            dt.Columns.Add("SUCCESS INS", typeof(string));
            dt.Columns.Add("ACCESS INS", typeof(string));
            dt.Columns.Add("SUCCESS UPD", typeof(string));
            dt.Columns.Add("ACCESS UPD", typeof(string));
            dt.Columns.Add("SUCCESS DEL", typeof(string));
            dt.Columns.Add("ACCESS DEL", typeof(string));

            DBConnectionSing db = null;
            string [] data = null;
            string[] options = { "SEL", "INS", "UPD", "DEL" };
            try
            {
                db = DBConnectionSing.Db;
                db.openConnection();
                                
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = db.conn;
                    cmd.CommandText = string.Format(ACTIVE_RULES_OBJ, (user.Equals("") ? " <> 'NULL'" : "= " + user));

                    using (OracleDataReader dr = cmd.ExecuteReader())
                    {
                        ds = new DataSet();
                        while (dr.Read())
                        {
                            DataRow rw = dt.NewRow();

                            rw["ACTION"] = false;
                            rw["PROPIETARIO"] = dr["OWNER"].ToString();
                            rw["NOMBRE OBJETO"] = dr["OBJECT_NAME"].ToString();
                            rw["TIPO OBJETO"] = dr["OBJECT_TYPE"].ToString();

                            for (int i = 4, j = 0; j < options.Length; i += 2, j++)
                            {
                                data = FixText(dr[options[j]].ToString());
                                rw[i] = data[0];
                                rw[i+1] = data[1];
                            }

                            /*data = FixText(dr["INS"].ToString());
                            rw[5] = data[0];
                            rw[6] = data[1];

                            data = FixText(dr["UPD"].ToString());
                            rw[7] = data[0];
                            rw[8] = data[1];

                            data = FixText(dr["DEL"].ToString());
                            rw[9] = data[0];
                            rw[10] = data[1];*/
                            dt.Rows.Add(rw);
                        }
                        dr.Close();
                    }
                }    
            }
            catch (OracleException e)
            {
                throw e;
            }
            finally
            {
                db.closeConnection();
                db = null;
            }
            ds.Tables.Add(dt);

            return ds;
        }

        public static DataSet GetActiveRulesStatements(string user)
        {
            DataSet ds = null;
            DataTable dt = new DataTable("TableActiveRulesStatements");

            dt.Columns.Add("ACTION", typeof(bool));
            dt.Columns.Add("USUARIO", typeof(string));
            dt.Columns.Add("OPCION AUDITADA", typeof(string));
            dt.Columns.Add("TRANSACCION EXITOSA", typeof(string));
            dt.Columns.Add("TRANSACCION FALLIDA", typeof(string));

            DBConnectionSing db = null;

            try
            {
                db = DBConnectionSing.Db;
                db.openConnection();

                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = db.conn;
                    cmd.CommandText = string.Format(ACTIVE_RULES_STMT, (user.Equals("") ? " <> 'NULL'" : " = " + user));

                    using (OracleDataReader dr = cmd.ExecuteReader())
                    {
                        ds = new DataSet();
                        while (dr.Read())
                        {
                            DataRow rw = dt.NewRow();
                            rw["ACTION"] = false;
                            rw["USUARIO"] = dr["USER_NAME"].ToString();
                            rw["OPCION AUDITADA"] = dr["AUDIT_OPTION"].ToString();
                            rw["TRANSACCION EXITOSA"] = dr["SUCCESS"].ToString();
                            rw["TRANSACCION FALLIDA"] = dr["FAILURE"].ToString();
                            dt.Rows.Add(rw);
                        }
                        dr.Close();
                    }
                }
            }
            catch (OracleException e)
            {
                throw e;
            }
            finally
            {
                db.closeConnection();
                db = null;
            }
            ds.Tables.Add(dt);

            return ds;
        }

        public static DataSet GetActiveRulesPrivilegies(string user)
        {
            DataSet ds = null;
            DataTable dt = new DataTable("TableActiveRulesPrivilegies");

            dt.Columns.Add("ACTION", typeof(bool));
            dt.Columns.Add("USUARIO", typeof(string));
            dt.Columns.Add("PRIVILEGIO", typeof(string));
            dt.Columns.Add("TRANSACCION EXITOSA", typeof(string));
            dt.Columns.Add("TRANSACCION FALLIDA", typeof(string));

            DBConnectionSing db = null;

            try
            {
                db = DBConnectionSing.Db;
                db.openConnection();

                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = db.conn;
                    cmd.CommandText = string.Format(ACTIVE_RULES_PRIV, (user.Equals("") ? " <> 'NULL'" : "= " + user));

                    using (OracleDataReader dr = cmd.ExecuteReader())
                    {
                        ds = new DataSet();
                        while (dr.Read())
                        {
                            DataRow rw = dt.NewRow();
                            rw["ACTION"] = false;
                            rw["USUARIO"] = dr["USER_NAME"].ToString();
                            rw["PRIVILEGIO"] = dr["PRIVILEGE"].ToString();
                            rw["TRANSACCION EXITOSA"] = dr["SUCCESS"].ToString();
                            rw["TRANSACCION FALLIDA"] = dr["FAILURE"].ToString();
                            dt.Rows.Add(rw);
                        }
                        dr.Close();
                    }
                }
            }
            catch (OracleException e)
            {
                throw e;
            }
            finally
            {
                db.closeConnection();
                db = null;
            }
            ds.Tables.Add(dt);

            return ds;
        }

        private static string[] FixText(string record)
        {
            //string res = "On Success = {0} / On Failure = {1}";              
            string[] data = record.Split('/');

            for (int i = 0; i < data.Length; i++)
            {
                switch (data[i].Trim().ToUpper())
                {
                    case "A":
                        data[i] = "Access";
                        break;
                    case "S":
                        data[i] = "Session";
                        break;
                    default:
                        data[i] = "Not Set";
                        break;
                }
            }
            return data;
        }

        public static bool DeactiveAuditRules(List<DataGridViewRow> lstRows)
        {
            DBConnectionSing db = null;
            bool result = false;
            List<string> Rules = new List<string>();
            Rules = AuditRules( lstRows);
            try
            {
                db = DBConnectionSing.Db;
                db.openConnection();

                OracleCommand cmd = new OracleCommand();
                cmd.Connection = db.conn;
                for (int i = 0; i < Rules.Count; i++)
                {
                    cmd.CommandText = Rules[i];

                    cmd.ExecuteNonQuery();

                }

                cmd = null;
                result = true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                db.closeConnection();
                db = null;
            }
            return result;
        }


        private static List<string> AuditRules(List<DataGridViewRow> lstRows)
        {
            List<string> auditRules = new List<string>();
            string target = string.Empty;
            foreach (DataGridViewRow r in lstRows)
            {
                if (r.Cells.Count == 5)
                {
                    auditRules.Add(string.Format("NOAUDIT {0} BY {1}", r.Cells[2].Value, r.Cells[1].Value));
                }
                else 
                {
                    target = AuditTarget(r);
                    auditRules.Add(string.Format("NOAUDIT {0} ON {1}.{2}", target, r.Cells[1].Value, r.Cells[2].Value));
                }
            }
            return auditRules;
        }

        private static string AuditTarget (DataGridViewRow r)
        {
            StringBuilder targets = new StringBuilder();
            List<string> values = new List<string>();
            bool select = false, update = false, delete = false, insert = false;
            foreach (DataGridViewCell cell in r.Cells) 
            {
                if (cell.ColumnIndex > 3)
                {
                    values.Add(cell.Value.ToString());
                }
            }         

            for (int i = 0; i < values.Count ; i ++) 
            {
                switch (i) 
                {
                    case 0:
                    case 1: 
                        {
                            if (!values[i].ToUpper().Equals("NOT SET")&&!select) 
                            {
                                targets.Append("SELECT,");
                                select = true;
                            }
                            break;
                        }
                    case 2:
                    case 3:
                        {
                            if (!values[i].ToUpper().Equals("NOT SET") && !insert)
                            {
                                targets.Append("INSERT,");
                                insert = true;
                            }
                            break;
                        }
                    case 4:
                    case 5:
                        {
                            if (!values[i].ToUpper().Equals("NOT SET") && !update)
                            {
                                targets.Append("UPDATE,");
                                update = true;
                            }
                            break;
                        }
                    case 6:
                    case 7:
                        {
                            if (!values[i].ToUpper().Equals("NOT SET") && !delete)
                            {
                                targets.Append("DELETE,");
                                delete = true;
                            }
                            break;
                        }
                    default: 
                        {
                            break;
                        }
                }
            }
            string ret = targets.Remove(targets.Length - 1, 1).ToString();
            return ret;
        }


        public static bool DeactiveObjectAuditRules(string target, List<string> rules)
        {
            bool res = false;
            DBConnectionSing db = null;

            try
            {
                db = DBConnectionSing.Db;
                db.openConnection();

                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = db.conn;
                    cmd.CommandText = DeactivateQuery(target, rules);

                    cmd.ExecuteNonQuery();
                    res = true;
                }
            }
            catch (OracleException e)
            {
                throw e;
            }
            finally
            {
                db.closeConnection();
                db = null;
            }

            return res;
        }

        private static string DeactivateQuery(string target, List<string> rules)
        {
            string query = string.Empty;
            StringBuilder actions = new StringBuilder();

            for (int i = 0; i < rules.Count; i++)
            {
                if (i != rules.Count - 1)
                {
                    actions.Append(string.Format("{0} ,", rules[i]));
                }
                else
                {
                    actions.Append(rules[i]);
                }
            }
            query = string.Format("NOAUDIT {0} ON {1}", actions.ToString(), target);
            return query;
        }
    }
}
